<?php


	Class Foxydb{

		/*** mysql hostname ***/
		var $hostname = 'localhost';

		/*** mysql username ***/
		var $username = 'root';

		/*** mysql password ***/
		var $password = '15317227';
		
		var $FETCH_MODE = PDO::FETCH_ASSOC;
		var $dbh;

		function open()
		{
			try {
				//for setting mysql charset, refer to http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html
				$this->dbh = new PDO("mysql:host=".$this->hostname.";dbname=foxysite;charset=utf8", 
									$this->username, $this->password);
				$this->dbh->query('SET NAMES utf8');
				/*** echo a message saying we have connected ***/
				//echo 'Connected to database foxysite successfully<br>';

				/*** set the error reporting attribute ***/
				$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			}catch(PDOException $e){
				echo $e->getMessage();
			}
		}

		function close()
		{
			/*** close the database connection ***/
			$this->dbh = null;
		}

		function findSiteByUri($uri)
		{
			try {
				$sql = " SELECT * FROM site WHERE uri=:uri";
				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':uri', $uri, PDO::PARAM_STR, strlen($uri));

				$stmt->execute() or die('Error, sql= [$sql] failed');;
				
				$result = $stmt->fetch($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function findCategoryByCatSid($catsid){
			try {
				$sql = " SELECT * FROM category WHERE sid=:catsid";
				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':catsid', $catsid, PDO::PARAM_INT);

				$stmt->execute() or die('Error, sql= [$sql] failed');;
				
				$result = $stmt->fetch($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function selectSitesByCatSid($catsid,$num=1,$start=0)
		{
			$result;
			try {
				$sql = "SELECT s.*,s2c.seqno seqno FROM site s,site2category s2c WHERE s.sid=s2c.sitesid AND s2c.catsid = :catsid ORDER BY s2c.seqno LIMIT :start, :end";


				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':catsid', $catsid, PDO::PARAM_INT);
				$stmt->bindParam(':start', $start, PDO::PARAM_INT);
				$end = $start + $num;
				$stmt->bindParam(':end', $end, PDO::PARAM_INT);

				$stmt->execute() or die('Error, sql=[$sql] failed');;

				$result = $stmt->fetchAll($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function findSiteCountByCatSid($catsid)
		{
			$result;
			try {
				$sql = "SELECT count(*) cnt FROM site2category  WHERE catsid = :catsid ";


				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':catsid', $catsid, PDO::PARAM_INT);

				$stmt->execute() or die('Error, sql=[$sql] failed');;

				$result = $stmt->fetch($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			if($result) {
				return $result["cnt"];
			}
			return 0;
		}

		function selectSite2CategoriesBySiteSid($sitesid)
		{
			$result;
			try {
				$sql = "SELECT * FROM site2category  WHERE sitesid = :sitesid ORDER BY seqno";
				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':sitesid', $sitesid, PDO::PARAM_INT);

				$stmt->execute() or die('Error, sql=[$sql] failed');;

				$result = $stmt->fetchAll($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}
		
		function selectSite2CategoriesByCateSid($catsid){
			$result;
			try {
				$sql = "SELECT * FROM site2category WHERE catsid = :catsid  ORDER BY seqno";			

				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':catsid', $catsid, PDO::PARAM_INT);

				$stmt->execute() or die('Error, sql=[$sql] failed');;

				$result = $stmt->fetchAll($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function findCategoryPrimaryPath($catsid){
			$result = array();
			$cat = $this->findCategoryByCatSid($catsid);
			$result[] = $cat;
			//Toppest category sid is 0
			$pcat = null;
			while($cat){
				$pcat = $cat;
				$cat = $this->findCategoryByCatSid($cat["psid"]);
				if($pcat == $cat) break;
				if($cat) $result[] = $cat;
				
			}
			return $result;
		}

		function getChildCategories($catsid){

			$result = array();
			try {
				$sql = " SELECT * FROM category WHERE psid=:catsid";
				$stmt = $this->dbh->prepare($sql);
				/*** bind the paramaters ***/
				$stmt->bindParam(':catsid', $catsid, PDO::PARAM_INT);

				$stmt->execute() or die('Error, sql= [$sql] failed');;
				
				$result = $stmt->fetchAll($this->FETCH_MODE);			
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function registerSite($puri){
			$uri = trim($puri);
			$site = $this->findSiteByUri($uri);
			if(!$site){
				try {
					$sql = "INSERT INTO site (primarycatsid,uri,name,description,searchpath,primaryareacatsid,contact)  values(0,:uri,'','','',0,'')";			
					if($sql){
						$stmt = $this->dbh->prepare($sql);
						$stmt->bindParam(':uri', $uri, PDO::PARAM_STR, strlen($uri));
						
						/* Begin a transaction, turning off autocommit */
						$this->dbh->beginTransaction();
						$result = $stmt->execute() or die('Error, sql= [$sql] failed');
						if($result){
							/* Commit the changes */
							$this->dbh->commit();
						}else{
							$this->dbh->rollBack();
						}
					}
				}catch(PDOException $e){
					echo $e->getMessage();
				}
				return $result;
			}
		}

		function updateSiteProfile($sitesid,$name,$desc,$contact,$searchpath){
			try {
				$sql = "UPDATE site SET name=:name, description=:desc, contact=:contact, searchpath=:searchpath WHERE sid=:sitesid";			
				if($sql){
					$stmt = $this->dbh->prepare($sql);
					$stmt->bindParam(':name', $name, PDO::PARAM_STR, strlen($name));
					$stmt->bindParam(':desc', $desc, PDO::PARAM_STR, strlen($desc));
					$stmt->bindParam(':contact', $contact, PDO::PARAM_STR, strlen($contact));
					$stmt->bindParam(':searchpath', $searchpath, PDO::PARAM_STR, strlen($searchpath));
					$stmt->bindParam(':sitesid', $sitesid, PDO::PARAM_INT);
					
					/* Begin a transaction, turning off autocommit */
					$this->dbh->beginTransaction();
					$result = $stmt->execute() or die('Error, sql= [$sql] failed');
					if($result){
						/* Commit the changes */
						$this->dbh->commit();
					}else{
						$this->dbh->rollBack();
					}
				}
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function updateSitePrimaryCategory($sitesid,$ptype,$pcatsid){
			try {
				if($ptype == 'content'){
					$sql = "UPDATE site SET primarycatsid = :primarycatsid WHERE sid= :sitesid";			
				}else if ($ptype == 'area'){
					$sql = "UPDATE site SET primaryareacatsid = :primarycatsid WHERE sid= :sitesid";			
				}
				if($sql){
					$stmt = $this->dbh->prepare($sql);
					$stmt->bindParam(':primarycatsid', $pcatsid, PDO::PARAM_INT);
					$stmt->bindParam(':sitesid', $sitesid, PDO::PARAM_INT);
					
					/* Begin a transaction, turning off autocommit */
					$this->dbh->beginTransaction();
					$result = $stmt->execute() or die('Error, sql= [$sql] failed');
					if($result){
						/* Commit the changes */
						$this->dbh->commit();
					}else{
						$this->dbh->rollBack();
					}
				}
			}catch(PDOException $e){
				echo $e->getMessage();
			}

			return $result;
		}

		function createMainLink(){
			try {
				$sql = "SELECT * FROM site";			

				$stmt = $this->dbh->prepare($sql);

				$stmt->execute() or die('Error, sql=[$sql] failed');

				while($row = $stmt->fetch($this->FETCH_MODE)){
					$insertSql = "insert into site2category (sitesid,catsid,seqno) values(".$row["sid"].",".$row["primarycatsid"].",0)";
					$ist = $this->dbh->prepare($insertSql);
					$ist->execute() or die('Error, sql=[$insertSql] failed');
				}

			}catch(PDOException $e){
				echo $e->getMessage();
			}
		}
	};

/*---------------------
//test cases:-

foreach(PDO::getAvailableDrivers() as $driver){
    echo $driver.'<br />';
}


$fdb = new Foxydb();
//1. open database
$fdb->open();

$site = $fdb->findSiteByUri("http://www.eetchina.com");

echo "<br>findSiteByUri<br>";
print_r($site);

$category= $fdb->findCategoryByCatSid(10111);
echo "<br>findCategoryByCatSid<br>";
print_r($category);

$sites = $fdb->selectSitesByCatSid(10111,5);
echo "<br>selectSitesByCatSid<br>";
print_r($sites);

$site2cats = $fdb->selectSite2CategoriesBySiteSid(10);
echo "<br>selectSite2CategoriesBySiteSid<br>";
print_r($site2cats);

$site2cats = $fdb->selectSite2CategoriesByCateSid(1011);
echo "<br>selectSite2CategoriesByCateSid<br>";
print_r($site2cats);

$path = $fdb->findCategoryPrimaryPath(10111);
echo "<br>findCategoryPrimaryPath<br>";
print_r($path);



//99. close database
$fdb->close();

---------------------------*/

?>